/**
 * 
 */
package DAO;

import java.sql.ResultSet;
import java.util.ArrayList;

import POJO.KhachHang;
import Util.DataProvider;

/**
 * @author THIEUQUANG
 * 
 */
public class KhachHangDAO
{
    /**
     * Lấy danh sách khách hàng
     * 
     * @return Danh sách KhachHang
     */
    public static ArrayList<KhachHang> SelectKhachHangALL()
    {
        ArrayList<KhachHang> listKhachHang = new ArrayList<KhachHang>();

        String sql = "SELECT * FROM KHACHHANG";

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();

            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                KhachHang kh = new KhachHang();

                kh.setMaKhachHang(rs.getInt("maKhachHang"));
                kh.setHoTen(rs.getNString("hoTen"));
                kh.setDiaChi(rs.getNString("diaChi"));
                kh.setEmail(rs.getString("email"));
                kh.setDienThoai(rs.getString("dienThoai"));
                kh.setTinhTrang(rs.getNString("tinhTrang"));
                kh.setDiemTichLuy(rs.getInt("diemTichLuy"));

                listKhachHang.add(kh);
            }

            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
        }
        return listKhachHang;
    }

    public static KhachHang SelectKhachHangById(String KhachHangID)
    {
        KhachHang kh = new KhachHang();

        try
        {
            String sql = "SELECT * FROM KHACHHANG WHERE MAKHACHHANG="
                    + KhachHangID;
            DataProvider dp = new DataProvider();
            dp.open();

            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                kh.setMaKhachHang(rs.getInt("maKhachHang"));
                kh.setHoTen(rs.getNString("hoTen"));
                kh.setDiaChi(rs.getNString("diaChi"));
                kh.setEmail(rs.getString("email"));
                kh.setDienThoai(rs.getString("dienThoai"));
                kh.setTinhTrang(rs.getNString("tinhTrang"));
                kh.setDiemTichLuy(rs.getInt("diemTichLuy"));
            }

            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kh;
    }

    public static boolean InsertKhachHang(KhachHang kh)
    {
        boolean kq = false;

        String sql = String
                .format("INSERT INTO KHACHHANG(MATKHAU, HOTEN, DIACHI, EMAIL, DIENTHOAI, TINHTRANG, DIEMTICHLUY) VALUES('NULL', N'%s', N'%s', '%s', '%s', N'%s', %d)",
                        kh.getHoTen(), kh.getDiaChi(), kh.getEmail(),
                        kh.getDienThoai(), kh.getTinhTrang(),
                        kh.getDiemTichLuy());

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static boolean UpdateKhachHang(KhachHang kh)
    {
        boolean kq = false;

        String sql = String
                .format("UPDATE KHACHHANG SET HOTEN=N'%s', DIACHI=N'%s', EMAIL='%s', TINHTRANG=N'%s', DIEMTICHLUY=%d WHERE MAKHACHHANG='%s'",
                        kh.getHoTen(), kh.getDiaChi(), kh.getEmail(),
                        kh.getTinhTrang(), kh.getDiemTichLuy(),
                        kh.getMaKhachHang());

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static boolean DeleteKhachHang(String KhachHangId)
    {
        boolean kq = false;

        String sql = "DELETE FROM KHACHHANG WHERE MAKHACHHANG=" + KhachHangId;

        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            int i = dp.executeUpdate(sql);
            if (i == 1)
                kq = true;
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }

        return kq;
    }

    public static ArrayList<KhachHang> SelectKhachHangByName(String name)
    {
        // TODO Auto-generated method stub
        ArrayList<KhachHang> list = new ArrayList<KhachHang>();
        
        String sql = "SELECT * FROM KHACHHANG WHERE HOTEN LIKE '%" + name+ "%'";
        
        try
        {
            DataProvider dp = new DataProvider();
            dp.open();
            
            ResultSet rs = dp.ExecuteQuery(sql);
            while (rs.next())
            {
                KhachHang kh = new KhachHang();
                
                kh.setMaKhachHang(rs.getInt("maKhachHang"));
                kh.setHoTen(rs.getNString("hoTen"));
                kh.setDiaChi(rs.getNString("diaChi"));
                kh.setEmail(rs.getString("email"));
                kh.setDienThoai(rs.getString("dienThoai"));
                kh.setTinhTrang(rs.getNString("tinhTrang"));
                kh.setDiemTichLuy(rs.getInt("diemTichLuy"));
                
                list.add(kh);
            }
            
            dp.close();
        }
        catch (Exception e)
        {
            // TODO: handle exception
            System.out.println(e.getMessage());
        }
        
        return list;
    }
}
